<?php
require_once "../vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
$inputFileType = 'Xlsx';
$inputFileName = '../doc.xlsx';
if(!is_file($inputFileName))
{
    echo 'file not exists!';
    die();
}
$reader = IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($inputFileName);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
$tables=array();
$table='';
$column='';
foreach($sheetData as $line)
{
    if(!empty($line['A']))
    {
        $table=$line['A'];
        $tables[$table]=array();
    }
    if(!empty($line['B']))
    {
        $column=$line['B'];
        $tables[$table][$column]=array();
    }
    $tables[$table][$column]['type']='String';
    if(!empty($line['C']))
    {
        $tables[$table][$column]['type']=$line['C'];
    }
    $tables[$table][$column]['must']='否';
    if(!empty($line['D']))
    {
        $tables[$table][$column]['must']=$line['D'];
    }
    if(!isset($tables[$table][$column]['comment']))
    {
        $tables[$table][$column]['comment']='';
    }
    else
    {
        $tables[$table][$column]['comment'].=" ";
    }
    $comment=$line['E'];
    if(!empty($line['F']))
    {
        $comment.=" ".$line['F'];
    }
    $tables[$table][$column]['comment'].=$comment;
}
$sql_create='';
$sql_alter='';

foreach($tables as $table=>$columninfo)
{
    $table="la_".$table;
    $sql_create.='create table if not exists `'.$table.'`($$$$'." ";
    $sql_create.='  `id` int(11) signed NOT NULL AUTO_INCREMENT,'." ";
    $sql_comment2="* @input Object $table $$$$";
    $sql_comment2.="* @output Object $table $$$$";
    $sql_comment="";
    $sql_comment1="";

    foreach($columninfo as $column=>$info)
    {
        $info['comment']=str_replace("'","\\'",$info['comment']);
        $info['comment']=str_replace("\\",'\\\\',$info['comment']);
        if($column=='id'
            || $column=='created_at'
            || $column=='updated_at'
            || $column=='deleted_at'
        )
        {
            continue;
        }
        $sentence="varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '".$info['comment']."'";
        if($info=='Integer')
        {
            $sentence="int(11) DEFAULT '0' COMMENT '".$info['comment']."'";
        }
        if($info=='Boolean')
        {
            $sentence="tinyint(4) DEFAULT '0' COMMENT '".$info['comment']."'";
        }
        $sql_create.='  `'.$column.'` '.$sentence.',$$$$';
        $sql_alter.="alter table `".$table.'` add column `'.$column.'` '.$sentence.''."\n";
        $sql_comment.="\$$column\t=\t\$data[\"$column\"]; \t//{$info['comment']} $$$$";
        $sql_comment1.="//\$data[\"$column\"]=\"\"; $$$$";
    }
    $sql_create.="  `created_at` datetime NOT NULL COMMENT '创建时间',$$$$";
    $sql_create.="  `updated_at` datetime NOT NULL COMMENT '更新时间',$$$$";
    $sql_create.="  `deleted_at` datetime NOT NULL COMMENT '删除时间',$$$$";
    $sql_alter.="alter table `".$table."` add column `created_at` datetime NOT NULL COMMENT '创建时间'"."\n";
    $sql_alter.="alter table `".$table."` add column `updated_at` datetime NOT NULL COMMENT '更新时间'"."\n";
    $sql_alter.="alter table `".$table."` add column `deleted_at` datetime NOT NULL COMMENT '删除时间'"."\n";
    $sql_comment.="\$created_at\t=\t\$data[\"created_at\"];\t//创建时间$$$$";
    $sql_comment.="\$updated_at\t=\t\$data[\"updated_at\"];\t//更新时间$$$$";
    $sql_comment.="\$deleted_at\t=\t\$data[\"deleted_at\"];\t//删除时间$$$$";
    $sql_comment1.="//\$data[\"created_at\"]=\"\"; $$$$";
    $sql_comment1.="//\$data[\"updated_at\"]=\"\"; $$$$";
    $sql_comment1.="//\$data[\"deleted_at\"]=\"\"; $$$$";
    $sql_comment.="";
    $sql_alter.="alter table `$table` comment='${sql_comment2}${sql_comment1}${sql_comment}'\n";
    $sql_create.='   PRIMARY KEY (`id`) USING BTREE $$$$';
    $sql_create.='  )ENGINE=Inn oDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'."\n";
}
$file_sql="";
foreach(explode("\n",$sql_create) as $line)
{
    if(empty($line))
    {
        continue;
    }
    $line=str_replace('$$$$',"\n",$line);
    if(class_exists('DB')){
        try
        {
            \DB::statement($line);
        }
        catch (\Exception $e)
        {

        }
    }
    $file_sql.=$line.";"."\n";
}
foreach(explode("\n",$sql_alter) as $line)
{
    if(empty($line))
    {
        continue;
    }
    $line=str_replace('$$$$',"\n",$line);
    if(class_exists('DB')){
        try
        {
            \DB::statement($line);
        }
        catch (\Exception $e)
        {

        }
    }
    $file_sql.=$line.";"."\n";
}
file_put_contents('doc.sql',$file_sql);
